package com.yycx.module.bus.provider.service.hy.impl;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import com.alibaba.fastjson.util.TypeUtils;
import com.baomidou.mybatisplus.core.toolkit.IdWorker;
import com.yycx.common.base.utils.FlymeUtils;
import com.yycx.common.mybatis.query.CriteriaUpdate;
import com.yycx.common.security.OpenHelper;
import com.yycx.common.utils.ApiAssert;
import com.yycx.common.utils.ExcelUtils;
import com.yycx.module.bus.client.entity.hy.BusBankStatement;
import com.yycx.module.bus.client.entity.BusCustom;
import com.yycx.module.bus.provider.mapper.hy.BusBankStatementMapper;
import com.yycx.module.bus.provider.service.hy.BusBankStatementService;
import com.yycx.common.mybatis.base.service.impl.BaseServiceImpl;
import com.yycx.common.mybatis.model.ResultBody;
import com.yycx.module.bus.provider.service.BusCustomService;
import com.yycx.module.system.client.entity.SysBank;
import com.yycx.module.system.provider.service.SysBankService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.yycx.common.base.entity.EntityMap;
import com.yycx.common.mybatis.query.CriteriaQuery;
import org.springframework.transaction.annotation.Propagation;
import com.yycx.common.mybatis.query.CriteriaSave;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.math.BigDecimal;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 银行流水接口实现类
 *
 * @author flyme
 * @date 2023-11-05
 */
@Service
@Transactional(rollbackFor = Exception.class)
public class BusBankStatementServiceImpl extends BaseServiceImpl<BusBankStatementMapper, BusBankStatement> implements BusBankStatementService {

    @Autowired
    private SysBankService bankService;

    @Autowired
    private BusCustomService customService;
    /**
     * 排除掉的付款记录卡号(测试)
     */
    private final static List<String> excludeBankNos100 = Arrays.asList(
        "1818050409028202037",
        "42050169613600000300",
        "42050110262300000277",
        "1818050819200030131",
        "210990600010019",
        "82010000003929117",
        "42001696108053003336",
        "127916822110801",
        "0706678611120100061995",
        "210990600010027",
        "82010000004348337"
    );

    /**
     * 排除掉的付款记录卡号(丹博瑞)
     */
    private final static List<String> excludeBankNos5 = Arrays.asList(
        "42050110262300000277",
        "1818050819200030131",
        "82010000003929117",
        "42001696108053003336",
        "127916822110801",
        "82010000004348337"
    );

    /**
     * 排除掉的付款记录卡号(赤壁)
     */
    private final static List<String> excludeBankNos1018 = Arrays.asList(
        "1818050409028202037",
        "42050169610800000545",
        "42050169613600000300",
        "16060230000000442",
        "42050110262300000277",
        "210990600010019",
        "210990600010027",
        "82010000004348337"
    );
    private final static Map<String, List<String>> excludeBankNosMap = new HashMap<>();

    static {
        excludeBankNosMap.put("excludeBankNos100", excludeBankNos100);
        excludeBankNosMap.put("excludeBankNos5", excludeBankNos5);
        excludeBankNosMap.put("excludeBankNos1018", excludeBankNos1018);
    }

    @Override
    public ResultBody beforeAdd(CriteriaSave cs, BusBankStatement bbs, EntityMap extra) {
        return ResultBody.ok();
    }


    @Override
    @Transactional(propagation = Propagation.NOT_SUPPORTED, readOnly = true)
    public ResultBody beforePageList(CriteriaQuery<BusBankStatement> cq, BusBankStatement bbs, EntityMap requestMap) {

        return ResultBody.ok();
    }


    @Override
    public Boolean clearPaymentId(String bankStatementId) {
        CriteriaUpdate cu = new CriteriaUpdate();
        cu.set("paymentId", null);
        cu.set("amountState", 0);
        cu.eq(true, "bankStatementId", bankStatementId);
        return update(cu);
    }

    @Override
    public Boolean clearByPaymentId(String paymentId) {
        CriteriaUpdate cu = new CriteriaUpdate();
        cu.set("paymentId", null);
        cu.set("amountState", 0);
        cu.eq(true, "paymentId", paymentId);
        return update(cu);
    }

    /**
     * 更新银行流水认款状态
     *
     * @param bankStatementId
     * @param paymentId
     */
    @Override
    public void updateBankStatement(String bankStatementId, Long paymentId, Integer amountState) {
        CriteriaUpdate cu = new CriteriaUpdate();
        cu.set("paymentId", paymentId);
        cu.set("amountState", amountState);
        cu.eq("bankStatementId", bankStatementId);
        update(cu);
    }

    @Override
    public ResultBody importData(MultipartFile file, Map params) {
        try {
            Long userId = OpenHelper.getUserId();
            Long bankId = TypeUtils.castToLong(params.get("bankId"));
            Integer commonTpl = TypeUtils.castToInt(params.get("commonTpl"));
            ApiAssert.isNotEmpty("请选择导入银行", bankId);
            SysBank sysBank = bankService.getById(bankId);
            Integer bankType = sysBank.getBankType();
            List<BusBankStatement> bankStatements = new ArrayList<>();
            Long companyId = sysBank.getCompanyId();
            List<String> excludeBankNos = excludeBankNosMap.get("excludeBankNos" + companyId.intValue());
            if (FlymeUtils.isNotEmpty(bankType) && commonTpl.equals(0)) {
                if (bankType.equals(1)) {
                    bankStatements = importJhBankDate(companyId, excludeBankNos, userId, sysBank, file);
                }
                if (bankType.equals(2)) {
                    bankStatements = importNsBankDate(companyId, excludeBankNos, userId, sysBank, file);
                }
                if (bankType.equals(3)) {
                    bankStatements = importGhBankDate(companyId, excludeBankNos, userId, sysBank, file);
                }
                if (bankType.equals(4)) {
                    bankStatements = importJhCdBankDate(companyId, excludeBankNos, userId, sysBank, file);
                }
                if (bankType.equals(5)) {
                    bankStatements = importZsCdBankDate(companyId, excludeBankNos, userId, sysBank, file);
                }
                if (bankType.equals(6)) {
                    bankStatements = importZsCdBankDate2(companyId, excludeBankNos, userId, sysBank, file);
                }
                if (bankType.equals(7)) {
                    bankStatements = importNhBankDate(companyId, excludeBankNos, userId, sysBank, file);
                }
                if (bankType.equals(8)) {
                    bankStatements = importZhCdBankDate(companyId, excludeBankNos, userId, sysBank, file);
                }
                if (bankType.equals(9)) {
                    bankStatements = importWjCdBankDate(companyId, excludeBankNos, userId, sysBank, file);
                }
            }else{
                bankStatements = importBankDate(companyId, excludeBankNos, userId, sysBank, file);
            }
            if (bankStatements.size() > 0) {
                boolean b = saveOrUpdateBatch(bankStatements);
                if (b) {
                    return ResultBody.msg("成功导入或者更新" + bankStatements.size() + "条记录");
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return ResultBody.failed("导入失败");
    }

    /**
     * 导入农商行数据
     */
    private List<BusBankStatement> importNsBankDate(Long companyId, List<String> excludeBankNos, Long userId, SysBank sysBank, MultipartFile file) throws Exception {
        ImportParams importParams = new ImportParams();
        importParams.setTitleRows(2);
        List<Map<Object, Object>> excelList2 = ExcelImportUtil.importExcel(file.getInputStream(), Map.class, importParams);
        List<BusBankStatement> bankStatements = new ArrayList<>();
        for (Map<Object, Object> objectMap : excelList2) {
            String statementNo = TypeUtils.castToString(objectMap.get("交易流水号"));
            Date amountDate = TypeUtils.castToDate(objectMap.get("交易日期"), "yyyy-MM-dd HH:mm:ss");
            BigDecimal amount = TypeUtils.castToBigDecimal(objectMap.get("贷方发生额"));
            String remark = FlymeUtils.getString(TypeUtils.castToString(objectMap.get("备注")), "");
            String bankNo = TypeUtils.castToString(objectMap.get("对方账号"));
            if (!excludeBankNos.contains(bankNo)) {
                if (FlymeUtils.gtzero(amount)) {
                    String customName = TypeUtils.castToString(objectMap.get("对方户名"));
                    String amountType = TypeUtils.castToString(objectMap.get("交易渠道"));
                    String describe = TypeUtils.castToString(objectMap.get("摘要"));
                    BusBankStatement bankStatement = getById(statementNo);
                    if (FlymeUtils.isEmpty(bankStatement)) {
                        bankStatement = new BusBankStatement();
                        BusCustom custom = customService.selectByCustomName(customName, companyId);
                        if (FlymeUtils.isNotEmpty(custom)) {
                            bankStatement.setCustomId(custom.getCustomId());
                            bankStatement.setCustomCode(custom.getCustomCode());
                            bankStatement.setSendArea(custom.getAreaName());
                        }
                        bankStatement.setStatementNo(statementNo);
                        bankStatement.setBankStatementId(statementNo);
                        bankStatement.setAmount(amount);
                        bankStatement.setBankNo(bankNo);
                        bankStatement.setAmountState(0);
                        bankStatement.setCustomName(customName);
                        bankStatement.setAmountType(amountType);
                        bankStatement.setAmountDate(amountDate);
                        bankStatement.setRemark(remark);
                        bankStatement.setUserId(userId);
                        bankStatement.setCompanyId(companyId);
                        bankStatement.setTakeBankNo(sysBank.getBankNo());
                        bankStatement.setTakeBankName(sysBank.getBankName());
                        bankStatement.setTakeBankId(sysBank.getBankId());
                        bankStatements.add(bankStatement);
                    }
                }
            }
        }
        return bankStatements;
    }

    /**
     * 导入建行数据
     */
    private List<BusBankStatement> importJhBankDate(Long companyId, List<String> excludeBankNos, Long userId, SysBank sysBank, MultipartFile file) throws Exception {
        ImportParams importParams = new ImportParams();
        importParams.setTitleRows(8);
        List<Map<Object, Object>> excelList2 = ExcelImportUtil.importExcel(file.getInputStream(), Map.class, importParams);
        List<BusBankStatement> bankStatements = new ArrayList<>();
        for (Map<Object, Object> objectMap : excelList2) {
            String statementNo = TypeUtils.castToString(objectMap.get("账户明细编号-交易流水号"));
            Date amountDate = TypeUtils.castToDate(objectMap.get("交易时间"), "yyyyMMdd HH:mm:ss");
            BigDecimal amount = TypeUtils.castToBigDecimal(objectMap.get("贷方发生额/元(收入)"));
            String bankNo = TypeUtils.castToString(objectMap.get("对方账号"));
            if (!excludeBankNos.contains(bankNo)) {
                if (FlymeUtils.gtzero(amount)) {
                    String customName = TypeUtils.castToString(objectMap.get("对方户名"));
                    String amountType = TypeUtils.castToString(objectMap.get("交易渠道"));
                    String describe = TypeUtils.castToString(objectMap.get("摘要"));
                    String remark = (String) objectMap.get("备注");
                    BusBankStatement bankStatement = getById(statementNo);
                    if (FlymeUtils.isEmpty(bankStatement)) {
                        bankStatement = new BusBankStatement();
                        BusCustom custom = customService.selectByCustomName(customName, companyId);
                        if (FlymeUtils.isNotEmpty(custom)) {
                            bankStatement.setCustomId(custom.getCustomId());
                            bankStatement.setCustomCode(custom.getCustomCode());
                            bankStatement.setSendArea(custom.getAreaName());
                        }
                        bankStatement.setStatementNo(statementNo);
                        bankStatement.setBankStatementId(statementNo);
                        bankStatement.setAmount(amount);
                        bankStatement.setBankNo(bankNo);
                        bankStatement.setAmountState(0);
                        bankStatement.setCustomName(customName);
                        bankStatement.setAmountType(amountType);
                        bankStatement.setAmountDate(amountDate);
                        bankStatement.setRemark(remark);
                        bankStatement.setUserId(userId);
                        bankStatement.setCompanyId(companyId);
                        bankStatement.setTakeBankNo(sysBank.getBankNo());
                        bankStatement.setTakeBankName(sysBank.getBankName());
                        bankStatement.setTakeBankId(sysBank.getBankId());
                        bankStatements.add(bankStatement);
                    }
                }
            }
        }
        return bankStatements;
    }

    /**
     * 导入工行数据
     */
    private List<BusBankStatement> importGhBankDate(Long companyId, List<String> excludeBankNos, Long userId, SysBank sysBank, MultipartFile file) throws Exception {
        ImportParams importParams = new ImportParams();
        importParams.setTitleRows(1);
        List<Map<Object, Object>> excelList2 = ExcelImportUtil.importExcel(file.getInputStream(), Map.class, importParams);
        List<BusBankStatement> bankStatements = new ArrayList<>();
        for (Map<Object, Object> objectMap : excelList2) {
            String amountText = TypeUtils.castToString(objectMap.get("转入金额"));
            if (FlymeUtils.isNotEmpty(amountText)) {
                String statementNo = TypeUtils.castToString(objectMap.get("顺序号"));
                statementNo=companyId+statementNo;
                Date amountDate = TypeUtils.castToDate(objectMap.get("交易时间"), "yyyy-MM-dd HH:mm:ss");
                BigDecimal amount = new BigDecimal(amountText.replace(",", ""));
                String bankNo = TypeUtils.castToString(objectMap.get("对方账号"));
                if (!excludeBankNos.contains(bankNo)) {
                    if (FlymeUtils.gtzero(amount)) {
                        String customName = TypeUtils.castToString(objectMap.get("对方单位"));
                        String amountType = TypeUtils.castToString(objectMap.get("交易渠道"));
                        String remark = TypeUtils.castToString(objectMap.get("摘要"));
                        BusBankStatement bankStatement = getById(statementNo);
                        if (FlymeUtils.isEmpty(bankStatement)) {
                            bankStatement = new BusBankStatement();
                            BusCustom custom = customService.selectByCustomName(customName, companyId);
                            if (FlymeUtils.isNotEmpty(custom)) {
                                bankStatement.setCustomId(custom.getCustomId());
                                bankStatement.setCustomCode(custom.getCustomCode());
                                bankStatement.setSendArea(custom.getAreaName());
                            }
                            bankStatement.setAmountState(0);
                            bankStatement.setStatementNo(statementNo);
                            bankStatement.setBankStatementId(statementNo);
                            bankStatement.setAmount(amount);
                            bankStatement.setBankNo(bankNo);
                            bankStatement.setCustomName(customName);
                            bankStatement.setAmountType(amountType);
                            bankStatement.setAmountDate(amountDate);
                            bankStatement.setRemark(remark);
                            bankStatement.setUserId(userId);
                            bankStatement.setCompanyId(companyId);
                            bankStatement.setTakeBankNo(sysBank.getBankNo());
                            bankStatement.setTakeBankName(sysBank.getBankName());
                            bankStatement.setTakeBankId(sysBank.getBankId());
                            bankStatements.add(bankStatement);
                        }
                    }
                }
            }
        }
        return bankStatements;
    }

    /**
     * 导入建行(承兑)数据
     */
    private List<BusBankStatement> importJhCdBankDate(Long companyId, List<String> excludeBankNos, Long userId, SysBank sysBank, MultipartFile file) throws Exception {
        ImportParams importParams = new ImportParams();
        importParams.setTitleRows(2);
        List<Map<Object, Object>> excelList2 = ExcelImportUtil.importExcel(file.getInputStream(), Map.class, importParams);
        List<BusBankStatement> bankStatements = new ArrayList<>();
        for (Map<Object, Object> objectMap : excelList2) {
            String statementNo = TypeUtils.castToString(objectMap.get("凭证号"));
            Date amountDate = TypeUtils.castToDate(objectMap.get("出票日期"), "yyyy-MM-dd");
            BigDecimal amount = TypeUtils.castToBigDecimal(objectMap.get("票据金额（元）"));
            if(FlymeUtils.isEmpty(amount)){
                amount=TypeUtils.castToBigDecimal(objectMap.get("票据(包)金额(元)"));
            }
            if (FlymeUtils.gtzero(amount)) {
                String customName = TypeUtils.castToString(objectMap.get("出票人"));
                BusBankStatement bankStatement = getById(statementNo);
                if (FlymeUtils.isEmpty(bankStatement)) {
                    bankStatement = new BusBankStatement();
                    BusCustom custom = customService.selectByCustomName(customName, companyId);
                    if (FlymeUtils.isNotEmpty(custom)) {
                        bankStatement.setCustomId(custom.getCustomId());
                        bankStatement.setCustomCode(custom.getCustomCode());
                        bankStatement.setSendArea(custom.getAreaName());
                    }
                    bankStatement.setStatementNo(statementNo);
                    bankStatement.setBankStatementId(statementNo);
                    bankStatement.setAmount(amount);
                    bankStatement.setBankNo("");
                    bankStatement.setAmountState(0);
                    bankStatement.setCustomName(customName);
                    bankStatement.setAmountType("");
                    bankStatement.setAmountDate(amountDate);
                    bankStatement.setRemark("");
                    bankStatement.setUserId(userId);
                    bankStatement.setCompanyId(companyId);
                    bankStatement.setTakeBankNo(sysBank.getBankNo());
                    bankStatement.setTakeBankName(sysBank.getBankName());
                    bankStatement.setTakeBankId(sysBank.getBankId());
                    bankStatements.add(bankStatement);
                }
            }

        }
        return bankStatements;
    }


    /**
     * 导入农行数据
     */
    private List<BusBankStatement> importNhBankDate(Long companyId, List<String> excludeBankNos, Long userId, SysBank sysBank, MultipartFile file) throws Exception {
        ImportParams importParams = new ImportParams();
        importParams.setTitleRows(2);
        List<Map<Object, Object>> excelList2 = ExcelImportUtil.importExcel(file.getInputStream(), Map.class, importParams);
        List<BusBankStatement> bankStatements = new ArrayList<>();
        for (Map<Object, Object> objectMap : excelList2) {
            String statementNo = TypeUtils.castToString(objectMap.get("交易日志号"));
            try{
                Date amountDate = TypeUtils.castToDate(objectMap.get("交易时间"), "yyyy-MM-dd");
                BigDecimal amount = TypeUtils.castToBigDecimal(objectMap.get("收入金额"));
                if (FlymeUtils.gtzero(amount)) {
                    String customName = TypeUtils.castToString(objectMap.get("对方户名"));
                    String bankNo = TypeUtils.castToString(objectMap.get("对方账号"));
                    BusBankStatement bankStatement = getById(statementNo);
                    if (FlymeUtils.isEmpty(bankStatement)) {
                        bankStatement = new BusBankStatement();
                        BusCustom custom = customService.selectByCustomName(customName, companyId);
                        if (FlymeUtils.isNotEmpty(custom)) {
                            bankStatement.setCustomId(custom.getCustomId());
                            bankStatement.setCustomCode(custom.getCustomCode());
                            bankStatement.setSendArea(custom.getAreaName());
                        }
                        bankStatement.setStatementNo(statementNo);
                        bankStatement.setBankStatementId(statementNo);
                        bankStatement.setAmount(amount);
                        bankStatement.setAmountState(0);
                        bankStatement.setCustomName(customName);
                        bankStatement.setAmountType("");
                        bankStatement.setBankNo(bankNo);
                        bankStatement.setAmountDate(amountDate);
                        bankStatement.setRemark("");
                        bankStatement.setUserId(userId);
                        bankStatement.setCompanyId(companyId);
                        bankStatement.setTakeBankNo(sysBank.getBankNo());
                        bankStatement.setTakeBankName(sysBank.getBankName());
                        bankStatement.setTakeBankId(sysBank.getBankId());
                        bankStatements.add(bankStatement);
                    }
                }
            }catch (Exception e){

            }

        }
        return bankStatements;
    }

    /**
     * 招商(承兑)
     */
    private List<BusBankStatement> importZsCdBankDate(Long companyId, List<String> excludeBankNos, Long userId, SysBank sysBank, MultipartFile file) throws Exception {
        ImportParams importParams = new ImportParams();
        importParams.setTitleRows(0);
        List<Map<Object, Object>> excelList2 = ExcelImportUtil.importExcel(file.getInputStream(), Map.class, importParams);
        List<BusBankStatement> bankStatements = new ArrayList<>();
        for (Map<Object, Object> objectMap : excelList2) {
            String amountText = TypeUtils.castToString(objectMap.get("票据金额"));
            if (FlymeUtils.isNotEmpty(amountText)) {
                String statementNo = TypeUtils.castToString(objectMap.get("电子票据号"));
                Date amountDate = TypeUtils.castToDate(objectMap.get("出票日"), "yyyy-MM-dd");
                BigDecimal amount = new BigDecimal(amountText.replace(",", ""));
                String bankNo = TypeUtils.castToString(objectMap.get("对手方账号"));
                if (!excludeBankNos.contains(bankNo)) {
                    if (FlymeUtils.gtzero(amount)) {
                        String customName = TypeUtils.castToString(objectMap.get("对手方名称"));
                        String amountType = TypeUtils.castToString(objectMap.get("票据种类"));
                        String remark = TypeUtils.castToString(objectMap.get("业务状态"));
                        BusBankStatement bankStatement = getById(statementNo);
                        if (FlymeUtils.isEmpty(bankStatement)) {
                            bankStatement = new BusBankStatement();
                            BusCustom custom = customService.selectByCustomName(customName, companyId);
                            if (FlymeUtils.isNotEmpty(custom)) {
                                bankStatement.setCustomId(custom.getCustomId());
                                bankStatement.setCustomCode(custom.getCustomCode());
                                bankStatement.setSendArea(custom.getAreaName());
                            }
                            bankStatement.setAmountState(0);
                            bankStatement.setStatementNo(statementNo);
                            bankStatement.setBankStatementId(statementNo);
                            bankStatement.setAmount(amount);
                            bankStatement.setBankNo(bankNo);
                            bankStatement.setCustomName(customName);
                            bankStatement.setAmountType(amountType);
                            bankStatement.setAmountDate(amountDate);
                            bankStatement.setRemark(remark);
                            bankStatement.setUserId(userId);
                            bankStatement.setCompanyId(companyId);
                            bankStatement.setTakeBankNo(sysBank.getBankNo());
                            bankStatement.setTakeBankName(sysBank.getBankName());
                            bankStatement.setTakeBankId(sysBank.getBankId());
                            bankStatements.add(bankStatement);
                        }
                    }
                }
            }
        }
        return bankStatements;
    }

    /**
     * 招商(承兑)新
     */
    private List<BusBankStatement> importZsCdBankDate2(Long companyId, List<String> excludeBankNos, Long userId, SysBank sysBank, MultipartFile file) throws Exception {
        ImportParams importParams = new ImportParams();
        importParams.setTitleRows(0);
        List<Map<Object, Object>> excelList2 = ExcelImportUtil.importExcel(file.getInputStream(), Map.class, importParams);
        List<BusBankStatement> bankStatements = new ArrayList<>();
        for (Map<Object, Object> objectMap : excelList2) {
            String amountText = TypeUtils.castToString(objectMap.get("收票时票面金额(元)"));
            if (FlymeUtils.isNotEmpty(amountText)) {
                String statementNo = TypeUtils.castToString(objectMap.get("票据(包)号"));
                Date amountDate = TypeUtils.castToDate(objectMap.get("收票时间"), "yyyy-MM-dd");
                BigDecimal amount = new BigDecimal(amountText.replace(",", ""));
                String bankNo = TypeUtils.castToString(objectMap.get("对手方账号"));
                if (!excludeBankNos.contains(bankNo)) {
                    if (FlymeUtils.gtzero(amount)) {
                        String customName = TypeUtils.castToString(objectMap.get("票据前手名称"));
                        String amountType = TypeUtils.castToString(objectMap.get("票据种类"));
                        String remark = TypeUtils.castToString(objectMap.get("签收账号"));
                        BusBankStatement bankStatement = getById(statementNo);
                        if (FlymeUtils.isEmpty(bankStatement)) {
                            bankStatement = new BusBankStatement();
                            BusCustom custom = customService.selectByCustomName(customName, companyId);
                            if (FlymeUtils.isNotEmpty(custom)) {
                                bankStatement.setCustomId(custom.getCustomId());
                                bankStatement.setCustomCode(custom.getCustomCode());
                                bankStatement.setSendArea(custom.getAreaName());
                            }
                            bankStatement.setAmountState(0);
                            bankStatement.setStatementNo(statementNo);
                            bankStatement.setBankStatementId(statementNo);
                            bankStatement.setAmount(amount);
                            bankStatement.setBankNo(bankNo);
                            bankStatement.setCustomName(customName);
                            bankStatement.setAmountType(amountType);
                            bankStatement.setAmountDate(amountDate);
                            bankStatement.setRemark(remark);
                            bankStatement.setUserId(userId);
                            bankStatement.setCompanyId(companyId);
                            bankStatement.setTakeBankNo(sysBank.getBankNo());
                            bankStatement.setTakeBankName(sysBank.getBankName());
                            bankStatement.setTakeBankId(sysBank.getBankId());
                            bankStatements.add(bankStatement);
                        }
                    }
                }
            }
        }
        return bankStatements;
    }

    /**
     * 中行
     */
    private List<BusBankStatement> importZhCdBankDate(Long companyId, List<String> excludeBankNos, Long userId, SysBank sysBank, MultipartFile file) throws Exception {
        ImportParams importParams = new ImportParams();
        importParams.setTitleRows(1);
        List<Map<Object, Object>> excelList2 = ExcelImportUtil.importExcel(file.getInputStream(), Map.class, importParams);
        List<BusBankStatement> bankStatements = new ArrayList<>();
        for (Map<Object, Object> objectMap : excelList2) {
            String amountText = TypeUtils.castToString(objectMap.get("交易金额"));
            if (FlymeUtils.isNotEmpty(amountText)) {
                String statementNo = TypeUtils.castToString(objectMap.get("电子票据号"));
                if(FlymeUtils.isEmpty(statementNo)){
                    statementNo= IdWorker.getIdStr();
                }
                Date amountDate = TypeUtils.castToDate(objectMap.get("交易日期"), "yyyy-MM-dd");
                BigDecimal amount = new BigDecimal(amountText.replace(",", ""));
                String bankNo = TypeUtils.castToString(objectMap.get("付款人账号"));
                if (!excludeBankNos.contains(bankNo)) {
                    if (FlymeUtils.gtzero(amount)) {
                        String customName = TypeUtils.castToString(objectMap.get("付款人名称"));
                        String amountType = TypeUtils.castToString(objectMap.get("业务类型"));
                        String remark = TypeUtils.castToString(objectMap.get("用途"));
                        BusBankStatement bankStatement = getById(statementNo);
                        if (FlymeUtils.isEmpty(bankStatement)) {
                            bankStatement = new BusBankStatement();
                            BusCustom custom = customService.selectByCustomName(customName, companyId);
                            if (FlymeUtils.isNotEmpty(custom)) {
                                bankStatement.setCustomId(custom.getCustomId());
                                bankStatement.setCustomCode(custom.getCustomCode());
                                bankStatement.setSendArea(custom.getAreaName());
                            }
                            bankStatement.setAmountState(0);
                            bankStatement.setStatementNo(statementNo);
                            bankStatement.setBankStatementId(statementNo);
                            bankStatement.setAmount(amount);
                            bankStatement.setBankNo(bankNo);
                            bankStatement.setCustomName(customName);
                            bankStatement.setAmountType(amountType);
                            bankStatement.setAmountDate(amountDate);
                            bankStatement.setRemark(remark);
                            bankStatement.setUserId(userId);
                            bankStatement.setCompanyId(companyId);
                            bankStatement.setTakeBankNo(sysBank.getBankNo());
                            bankStatement.setTakeBankName(sysBank.getBankName());
                            bankStatement.setTakeBankId(sysBank.getBankId());
                            bankStatements.add(bankStatement);
                        }
                    }
                }
            }
        }
        return bankStatements;
    }

    /**
     * 吴江
     */
    private List<BusBankStatement> importWjCdBankDate(Long companyId, List<String> excludeBankNos, Long userId, SysBank sysBank, MultipartFile file) throws Exception {
        ImportParams importParams = new ImportParams();
        importParams.setTitleRows(4);
        List<Map<Object, Object>> excelList2 = ExcelImportUtil.importExcel(file.getInputStream(), Map.class, importParams);
        List<BusBankStatement> bankStatements = new ArrayList<>();
        for (Map<Object, Object> objectMap : excelList2) {
            String amountText = TypeUtils.castToString(objectMap.get("收入"));
            if (FlymeUtils.isNotEmpty(amountText)) {
                String statementNo = TypeUtils.castToString(objectMap.get("电子票据号"));
                if(FlymeUtils.isEmpty(statementNo)){
                    statementNo= IdWorker.getIdStr();
                }
                Date amountDate = TypeUtils.castToDate(objectMap.get("交易日期"), "yyyy-MM-dd");
                BigDecimal amount = new BigDecimal(amountText.replace(",", ""));
                String bankNo = TypeUtils.castToString(objectMap.get("对方账号"));
                if (!excludeBankNos.contains(bankNo)) {
                    if (FlymeUtils.gtzero(amount)) {
                        String customName = TypeUtils.castToString(objectMap.get("对方信息"));
                        String amountType = TypeUtils.castToString(objectMap.get("业务类型"));
                        String remark = TypeUtils.castToString(objectMap.get("摘要"));
                        BusBankStatement bankStatement = getById(statementNo);
                        if (FlymeUtils.isEmpty(bankStatement)) {
                            bankStatement = new BusBankStatement();
                            BusCustom custom = customService.selectByCustomName(customName, companyId);
                            if (FlymeUtils.isNotEmpty(custom)) {
                                bankStatement.setCustomId(custom.getCustomId());
                                bankStatement.setCustomCode(custom.getCustomCode());
                                bankStatement.setSendArea(custom.getAreaName());
                            }
                            bankStatement.setAmountState(0);
                            bankStatement.setStatementNo(statementNo);
                            bankStatement.setBankStatementId(statementNo);
                            bankStatement.setAmount(amount);
                            bankStatement.setBankNo(bankNo);
                            bankStatement.setCustomName(customName);
                            bankStatement.setAmountType(amountType);
                            bankStatement.setAmountDate(amountDate);
                            bankStatement.setRemark(remark);
                            bankStatement.setUserId(userId);
                            bankStatement.setCompanyId(companyId);
                            bankStatement.setTakeBankNo(sysBank.getBankNo());
                            bankStatement.setTakeBankName(sysBank.getBankName());
                            bankStatement.setTakeBankId(sysBank.getBankId());
                            bankStatements.add(bankStatement);
                        }
                    }
                }
            }
        }
        return bankStatements;
    }


    /**
     * 通用模版
     */
    private List<BusBankStatement> importBankDate(Long companyId, List<String> excludeBankNos, Long userId, SysBank sysBank, MultipartFile file) throws Exception {
        ImportParams importParams = new ImportParams();
        importParams.setTitleRows(0);
        List<Map<Object, Object>> excelList2 = ExcelImportUtil.importExcel(file.getInputStream(), Map.class, importParams);
        List<BusBankStatement> bankStatements = new ArrayList<>();
        for (Map<Object, Object> objectMap : excelList2) {
            String amountText = TypeUtils.castToString(objectMap.get("交易金额"));
            if (FlymeUtils.isNotEmpty(amountText)) {
                String statementNo = TypeUtils.castToString(objectMap.get("交易流水号"));
                if(FlymeUtils.isEmpty(statementNo)){
                    statementNo= IdWorker.getIdStr();
                }
                Date amountDate = TypeUtils.castToDate(objectMap.get("交易日期"), "yyyy-MM-dd");
                BigDecimal amount = new BigDecimal(amountText.replace(",", ""));
                String bankNo = TypeUtils.castToString(objectMap.get("付款人账号"));
                if (!excludeBankNos.contains(bankNo)) {
                    if (FlymeUtils.gtzero(amount)) {
                        String customName = TypeUtils.castToString(objectMap.get("付款人名称"));
                        String remark = TypeUtils.castToString(objectMap.get("备注"));
                        BusBankStatement bankStatement = getById(statementNo);
                        if (FlymeUtils.isEmpty(bankStatement)) {
                            bankStatement = new BusBankStatement();
                            BusCustom custom = customService.selectByCustomName(customName, companyId);
                            if (FlymeUtils.isNotEmpty(custom)) {
                                bankStatement.setCustomId(custom.getCustomId());
                                bankStatement.setCustomCode(custom.getCustomCode());
                                bankStatement.setSendArea(custom.getAreaName());
                            }
                            bankStatement.setAmountState(0);
                            bankStatement.setStatementNo(statementNo);
                            bankStatement.setBankStatementId(statementNo);
                            bankStatement.setAmount(amount);
                            bankStatement.setBankNo(bankNo);
                            bankStatement.setCustomName(customName);
                            bankStatement.setAmountDate(amountDate);
                            bankStatement.setRemark(remark);
                            bankStatement.setUserId(userId);
                            bankStatement.setCompanyId(companyId);
                            bankStatement.setTakeBankNo(sysBank.getBankNo());
                            bankStatement.setTakeBankName(sysBank.getBankName());
                            bankStatement.setTakeBankId(sysBank.getBankId());
                            bankStatements.add(bankStatement);
                        }
                    }
                }
            }
        }
        return bankStatements;
    }


    private String getStatementNo(String text, Date amountDate) {
        String pattern = "<支付交易序号>(.+?)</支付交易序号>";
        Pattern r = Pattern.compile(pattern);
        Matcher m = r.matcher(text);
        String statementNo = amountDate.getTime() + "";
        if (m.find()) {
            statementNo = m.group(1);
        }
        return statementNo;
    }
}
